#! /usr/bin/env python
#
# Copyright (C) 2011-2013 Christian T. Steigies <steigies@physik.uni-kiel.de>
#
# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

"""mysql helper functions"""

__author__ = "Christian T. Steigies <steigies@physik.uni-kiel.de>"
__license__ = "GPL License"

from nmdb.nmdb.nmdb_format import ori_format, rev_format, hour_format, meta_format
#from nmdb.tools.mysql import mysql_values

## --------------------------------------------------------------------------
def nmdb_timestamp(cursor):
    """get current timestamp from database server"""

    qstring = "SELECT CURRENT_TIMESTAMP()"
    cursor.execute(qstring)
    value = cursor.fetchone()

    return value[0]


## --------------------------------------------------------------------------
def nmdb_stations(cursor, table):
    """get list of stations for table extension"""
    
    schema = "FROM information_schema.TABLES where TABLE_NAME like '%%\_%s' AND TABLE_SCHEMA='nmdb' ORDER BY TABLE_NAME;" % (table)
    #qstring = "SELECT count(TABLE_NAME) %s" % (schema)
    #c.execute(qstring)
    #count = int(c.fetchone()[0])
    qstring = "SELECT TABLE_NAME %s" % (schema)
    cursor.execute(qstring)
    stations_table = cursor.fetchall()
    stations = []
    for i in range(0, len(stations_table)):
        stations.append(stations_table[i][0])

    return stations

    
## --------------------------------------------------------------------------
def query(name, version, mode):
    if mode in ["ori", "1m"]:
        value = query_1m(name, version)
    elif mode in ["1h"]:
        value = query_1h(name, version)
    else:
        raise ValueError  # illegal mode

    return value
## --------------------------------------------------------------------------

def query_1h(name, version):
    """
    create write query for 1h data
    use REPLACE only when version == 1
    TODO rename to insert_1h?
    """

    if version == 0:  # initial write
        value = ("INSERT INTO %s_1h (%s)") % (name.upper(), hour_format)

    elif version == 1:  # overwrite
        value = ("REPLACE INTO %s_1h (%s)") % (name.upper(), hour_format)

    else:
        print name, version
        raise ValueError  # illegal version number

    return value


## --------------------------------------------------------------------------
def query_1m(name, version):
    """
    create write query for ori, rev, based on version number
    TODO rename to insert_revori?
    """

    if version == 0:  # version = "ori"
        value = ("INSERT INTO %s_ori (%s)") % (name.upper(), ori_format)

    elif  version == 1:  # rev version 1
        value = ("INSERT INTO %s_rev (%s)") % (name.upper(), rev_format)

    elif version > 1:  # rev version 2 or higher
        value = ("REPLACE INTO %s_rev (%s)") % (name.upper(), rev_format)

    else:
        raise ValueError  # illegal version number

    return value


## --------------------------------------------------------------------------
def read_meta_id(cursor, station):
    """return last used id in meta table"""
    
    querystring = "SELECT MAX(ID_RECORD) FROM %s_meta" % (station.upper())
    cursor.execute(querystring)
    answer = cursor.fetchone()

    if answer == None:
        value = 0
    elif answer[0] == None:
        value = 0
    else:
        value = int(answer[0])

    return value


## --------------------------------------------------------------------------
def insert_meta(name, start, end, quality, comment, idnum):
    """
    create write query for meta table
    """

    value = ("REPLACE INTO %s_meta (%s) VALUES ") % (name.upper(), meta_format)
    value += "(\"%s\", \"%s\", %s, \"%s\", %s)" % (start, end, quality, comment, idnum)

    return value

## --------------------------------------------------------------------------
if __name__ == '__main__':
    print "This is the mysql_query module."
    #unittest.main()
    from nmdb.tools.datetool import str2datetime

    print hour_format
    print meta_format
    name = "invk"
    start = str2datetime("2011-01-01 00:00:00")
    end = str2datetime("2012-01-01 00:00:00")
    quality = 1
    comment = "x"
    idnum = 0
    print insert_meta(name, start, end, quality, comment, idnum)
